'Check SCCM TempDB Space.vbs
'Alan dot Kaplan at VA dot Gov.
'this will connect to an SCCM parent primary database, and get TempDB freespace for server, plus
'all of its site servers 

Option Explicit 
On Error Resume Next
Dim message, strComputer
Dim objWMIService, colitems, objitem, wmi, obj, strNameSpace
Dim WshShell: Set WshShell = WScript.CreateObject("WScript.Shell")
Dim objFSO: Set objFSO=CreateObject("Scripting.FileSystemObject")
Const adOpenStatic = 3
Const adLockOptimistic = 3
Dim locator
Dim oSMSWMIConn

If (Not IsCScript()) Then 		'If not CScript, re-run with cscript...
	dim quote: quote=chr(34)
    Dim strCmdLine, Argument
    
    strCmdLine = WScript.Path & "\cscript.exe //NOLOGO " & quote & WScript.scriptFullName & Quote
    
    If Wscript.Arguments.Count > 0 Then
        For Each Argument in Wscript.Arguments
            strCmdLine = strCmdLine & space(1) & quote & Argument & quote
        Next 
    End If
    
    oWShell.Run strCmdLine,1,False
    WScript.Quit            	'...and stop running as WScript    
End If


If WScript.Arguments.Count = 1 Then
	strComputer = WScript.Arguments(0)
Else
	strComputer = InputBox("Check free space for sites systems of what SCCM server?","SCCM Database Server Name",strComputer)
End If 

If strcomputer = "" Then WScript.Quit
strComputer = UCase(strComputer)

Dim fso,logfile, appendout
logfile = wshShell.ExpandEnvironmentStrings("%userprofile%") & "\desktop\TempDBSpace.xls"

'setup log
Const ForAppend = 8
set fso = CreateObject("Scripting.FileSystemObject")
If fso.FileExists(logfile) then fso.DeleteFile logfile

set AppendOut = fso.OpenTextFile(logfile, ForAppend, True)
appendout.WriteLine "Server	SQL Last Started	DB Path	TempDB Size(MB)	DB Drive FreeSpace(MB)"


Set locator = CreateObject("WbemScripting.SWbemLocator")
Set wmi = locator.ConnectServer(strComputer, "root\sms")
For Each obj In wmi.InstancesOf("__NAMESPACE")
	If InStr(obj.Name,"site") > 0 Then 
	strNameSpace = "\root\sms\" & obj.Name
	End If 
Next

Set objWMIService = GetObject("winmgmts:\\" & strComputer & strNamespace)
If Err <> 0 Then WScript.Echo Err.Description
Set colItems = objWMIService.ExecQuery("SELECT * FROM SMS_SystemResourceList where RoleName = 'SMS Site Server'",,48)

For Each objitem In colitems
  GetInfo objItem.ServerName, objitem.siteCode
Next

appendout.Close
oSMSWMIConn.Close

SaveAsExcel(logfile)
MsgBox "Saved log file as: " & logfile,vbOKOnly,"Done"

'================== Functions and Subs  ==========================
Sub GetInfo(strServer, strSite)
	Dim strQuery, strLastStart, strDBPath, iTempDBSize, iFreeSpace, TempDBFreeSpace
	InitSqL strServer, strSite
	
	'last time SQL started
	strQuery = "select crdate  from [master].dbo.sysdatabases where name='tempdb'"
	strLastStart = RunSQL(strQuery)
	
	'temp dbPath
	strQuery = "select filename from [master].dbo.sysdatabases where name='tempdb'"
	strDBPath = RunSQL(strQuery)
	iFreeSpace = GetFreeSpace(strServer, strDBPath)

	'TempDB FileSize
	strQuery = "SELECT SUM(size)*1.0/128 AS [tempdbsize] FROM tempdb.sys.database_files"
	iTempDBSize = RunSQL(strQuery)

	'unallocated free space tempdb 
	'strQuery = "Select (SUM(unallocated_extent_page_count)*1.0/128) AS [TempDBFreeSpace] FROM sys.dm_db_file_space_usage"
	'TempDBFreeSpace = RunSQL(strQuery)
	
	EchoAndLog strServer & vbTab & strLastStart & vbtab &  strDBPath & vbtab &  iTempDBSize & vbTab &  iFreeSpace  
End Sub 

	
Sub InitSQL(strServer, strSite)
	'open a SQL connection to server for queries

	Set oSMSWMIConn = CreateObject("ADODB.Connection")
	WScript.Echo "Connecting to SMS Database on " & strServer
	On Error Resume Next
	oSMSWMIConn.Open _
	    "Provider = SQLOLEDB;" &_
	    "Data Source =" & strServer & ";"&_
	    "Initial Catalog=SMS_" & strSite & ";"&_
	    "INTEGRATED SECURITY=sspi;"
		If Err <> 0 Then
			MsgBox Err.Description,vbCritical & vbOKOnly,"Error"
			WScript.Quit
		End If 
	On Error GoTo 0
End Sub 

Function RunSQL(strQuery)
	'Run Query
	'wscript.echo strQuery 
	Dim strCollection, strSQL
	strSQL = strQuery 
	Dim sqLRS: Set sqlRS = CreateObject("ADODB.Recordset")
	'open cx to SMS SQL
	sqlRS.Open strQuery, oSMSWMIConn, adOpenStatic, adLockOptimistic
	
	'WScript.Echo "Starting SMS Query"
	On Error resume Next
	Do Until sqlRS.EOF
		RunSQL = sqLRS.Fields(0)
		sqlRS.Movenext
	Loop
	
	On Error goto 0
	'WScript.Echo "Finished SMS Query"
	
	sqlRS.Close
End Function 


Function GetFreeSpace(strServer, strPath)
	Dim strDrive, oWMI, colItems, objitem
	
	'Get drive from path
	Dim tArray
	tArray =split(strpath,":")
	strDrive = tArray(0)& ":"
	
	On Error Resume Next
	Set oWMI = GetObject("winmgmts:\\" & strServer & "\root\cimv2")
	If Err <> 0 Then
		MsgBox Err.Description,vbokonly,"Error"
		WScript.Quit(0)
	End If
	
	Set colItems = oWMI.ExecQuery("Select freespace from Win32_LogicalDisk where caption='" & strDrive & "'",,48)
	For Each objitem in colitems	'Get free space on drive
		GetFreeSpace = objitem.freeSpace *1.0/128
		Exit For 
	Next

End Function 

Sub EchoAndLog (message)
'Echo output and write to log
	Wscript.Echo message
	AppendOut.WriteLine message
End Sub  

Function IsCScript()
    If (InStr(UCase(WScript.FullName), "CSCRIPT") <> 0) Then
        IsCScript = True
    Else
        IsCScript = False
    End If
End Function

	
Sub SaveAsExcel(strFileName)
	'Original file is tab delimited text.  Convert to XLS if Excel installed
	Const xlnormal = -4143
	Const xlAscending = 1
	Const xlDescending = 2
	Const xlYes = 1
	const xlSortValues = 1

	Dim fso, oXL, objRange
	Set fso = CreateObject("Scripting.FileSystemObject")
	If Not fso.FileExists(strFileName) Then WScript.Quit
	On Error Resume Next
	Set oXL = CreateObject("Excel.Application")
	
	If Err <> 0 Then 'Excel not installed
		Err.Clear 	'Rename to /TXT if no Excel
		Dim strNewName: strNewName = Replace(".xls", ".txt")
		fso.MoveFile strFileName, strNewName
		logfile = strNewName
		On Error GoTo 0
		Exit Sub
	End If 
	 
	'oXL.Visible = True
	oXL.DisplayAlerts=False ' don't display overwrite prompt.
	oXL.Workbooks.Open(strFileName)
	Set objRange = oXL.Worksheets(1).UsedRange
	Set objRange2 = oXL.Range("A2")
	
	objRange.Sort objRange2, xlAscending,,,,,, xlYes
	objRange.EntireColumn.Autofit()

	Dim oWS	
	Set oWS = oXL.Worksheets(1)
	oWS.Activate
	oWS.Name = sdomain
	
	oXL.ActiveWorkBook.SaveAs strFileName,xlnormal,,,,,,,True 'overwrite existing
	oXL.ActiveWorkBook.Close
	oXL.Quit
	'WScript.Echo "Done"
End Sub